#!/usr/bin/env bash

dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -uroot -proot -hhadoop2<<EOF
use sqlserver;
create TEMPORARY table mid_notoilRate_vip
select substr(transaction_date,1,13) minutes,station_id stationid,count(*) tradenumber
from realtime.transactions_vip
group by substr(transaction_date,1,13),station_id;

select A.houra,A.stationID,A.numbera,B.numberb
FROM
      (select substr(SENDTIME,1,13)AS houra,STATIONCODE stationID,COUNT(*) AS numbera
      FROM realtime.TBL_ORDERDETAILS_ALL WHERE LITRE = 0 and ORDER_DEAL = 4
      GROUP BY substr(SENDTIME,1,16),STATIONCODE) AS A
JOIN
(select substr(minutes,1,13)AS hourb,stationID,sum(tradeNumber)AS numberb
from mid_notoilRate_vip
group by substr(minutes,1,13),stationID) AS B
ON A.houra=B.hourb and A.stationID=B.stationID;
EOF